﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DataLayer;
using System.Windows.Forms;

namespace Manager
{
    public class DbMikro
    {
        public string MikroServer { get; set; }
        public string MikroDb { get; set; }
        public int Depo { get; set; }
        public string SormMerkezi { get; set; }
        public string Sifre { get; set; }
        Dictionary<string, string[]> stokkodlari = new Dictionary<string, string[]>();
        decimal usd_kur = 1;
        decimal euro_kur = 1;
        public string Tedarikci { get; set; }

        SqlConnection MikroCnn;

        private decimal DovizKur(string parabirimi)
        {
            object obj = Factory.GetFactory.ExecuteScaler("SELECT     rate  FROM         CURRENCY  WHERE     (name = '" + parabirimi + "')");
            decimal kur = 1;
            if (obj != null)
                kur = Convert.ToDecimal(obj);
            else
                kur = 1;
            return kur;
        }

        public bool SiparisAktar(string seri, int sira, string carikod, int doviz)
        {
            bool sonuc = false;
            try
            {


                IDbDataParameter prm1 = Factory.GetFactory.GetParameter("@seri", DbType.String);
                IDbDataParameter prm2 = Factory.GetFactory.GetParameter("@sira", DbType.Int32);
                prm1.Value = seri;
                prm2.Value = sira;

                DataTable dt = Factory.GetFactory.ExecuteDataTable(CommandType.Text, "SELECT     rec_id, order_date, customer_code, product_code, barcode, amount, price, total,discount1,discount2,discount3 FROM         ORDER_DETAILS  WHERE  (barcode is not null) and  (order_series = @seri) AND (order_no = @sira)", prm1, prm2);

                DateTime tarih = DateTime.Now;
                int satir = 0;
                string stokkod = string.Empty;
                decimal miktar = 0;
                string plasiyer = string.Empty;
                decimal toplam;
                int kdv;
                decimal fiyat;
                string barkod = string.Empty;
                decimal isk1;
                decimal isk2;
                decimal isk3;
                decimal isk1Tutar = 0;
                decimal isk2Tutar = 0;
                decimal isk3Tutar = 0;
                string evrak_seri = seri;
                int evrak_sira;
                evrak_sira = sira; //mikroEvrakSira(evrak_seri);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    stokkod = dt.Rows[i]["product_code"].ToString();
                    if (!stokkodlari.ContainsKey(stokkod))
                    {
                        MessageBox.Show(stokkod + " bulunamadı");
                        return false;
                    }
                }

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    tarih = Convert.ToDateTime(dt.Rows[i]["order_date"]);
                    stokkod = dt.Rows[i]["product_code"].ToString();
                    satir = i;
                    miktar = Convert.ToDecimal(dt.Rows[i]["amount"]);
                    fiyat = Convert.ToDecimal(dt.Rows[i]["price"]);
                    toplam = miktar * fiyat;
                    barkod = dt.Rows[i]["barcode"].ToString();
                    isk1 = Convert.ToDecimal(dt.Rows[i]["discount1"]);
                    isk2 = Convert.ToDecimal(dt.Rows[i]["discount2"]);
                    isk3 = Convert.ToDecimal(dt.Rows[i]["discount3"]);

                    isk1Tutar = toplam / 100m * isk1;
                    isk2Tutar = (toplam - isk1Tutar) / 100m * isk2;
                    isk3Tutar = (toplam - isk1Tutar - isk2Tutar) / 100m * isk3;

                    switch (doviz)
                    {
                        case 1:
                            toplam = toplam / usd_kur;
                            fiyat = toplam / miktar;
                            break;
                        case 2:
                            toplam = toplam / euro_kur;
                            fiyat = toplam / miktar;
                            break;
                        default:
                            break;
                    }

                    if (stokkodlari.ContainsKey(stokkod)) kdv = int.Parse(stokkodlari[stokkod][1]); else kdv = 0;
                    if (stokkodlari.ContainsKey(stokkod)) stokkod = stokkodlari[stokkod][0];

                    VerilenSiparisEkle(tarih, evrak_seri, evrak_sira, satir, stokkod, carikod, miktar, Depo.ToString(), Depo.ToString(), "", SormMerkezi, "", toplam, isk1Tutar, isk2Tutar, isk3Tutar, 0, 0, kdv, fiyat, "", "0", "0", doviz);

                }
                sonuc = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                sonuc = false;
            }
            return sonuc;
        }

        public void EvrakAktar()
        {
            barkodKontrol();

            usd_kur = DovizKur("USD");
            euro_kur = DovizKur("EURO");

            StringBuilder sb = new StringBuilder();
            sb.Append("exec sp_executesql N'SELECT     order_series, order_no, customer_code ");
            sb.Append(" FROM         ORDER_DETAILS ");
            sb.AppendFormat(" WHERE supplier =''{0}'' and gonderildi =0", Tedarikci);
            sb.Append(" GROUP BY order_series, order_no, customer_code'");

            DataTable evraklar = Factory.GetFactory.ExecuteDataTable(sb.ToString());
            string seri;
            int sira;
            string cari;
            int doviz;
            try
            {
                MikroCnn = new SqlConnection(string.Format("Data Source={0};Database={1};user id=sa;password={2}", this.MikroServer, this.MikroDb, this.Sifre));
                MikroCnn.Open();

                for (int i = 0; i < evraklar.Rows.Count; i++)
                {
                    seri = evraklar.Rows[i]["order_series"].ToString();
                    sira = Convert.ToInt32(evraklar.Rows[i]["order_no"]);
                    cari = evraklar.Rows[i]["customer_code"].ToString();
                    doviz = 0;

                    if (SiparisAktar(seri, sira, cari, doviz))
                    {
                        Factory.GetFactory.ExecuteNonQuery(string.Format("exec sp_executesql N'update ORDER_DETAILS set gonderildi=1 where (order_series = ''{0}'') AND (order_no = {1})'", seri, sira));
                    }
                }
                MikroCnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            MessageBox.Show("Aktarım Tamamlandı");
        }

        void barkodKontrol()
        {
            SqlConnection cn = new SqlConnection(string.Format("Data Source={0};Database={1};user id=sa;password={2}", this.MikroServer, this.MikroDb, this.Sifre));
            cn.Open();
            //SqlCommand cmd = new SqlCommand("SELECT     BARKOD_TANIMLARI.bar_kodu,BARKOD_TANIMLARI.bar_stokkodu,STOKLAR.sto_toptan_vergi FROM   BARKOD_TANIMLARI INNER JOIN  STOKLAR ON BARKOD_TANIMLARI.bar_stokkodu = STOKLAR.sto_kod", cn);
            SqlCommand cmd = new SqlCommand("SELECT     sto_kod, sto_toptan_vergi  FROM         STOKLAR", cn);

            cmd.CommandTimeout = int.MaxValue;
            SqlDataReader rd = cmd.ExecuteReader();
            //Dictionary<string, string> dic1 = new Dictionary<string, string>();
            Dictionary<string, string> dic2 = new Dictionary<string, string>();

            while (rd.Read())
            {
                //if (!dic1.ContainsKey(rd.GetString(0).Trim()))
                //    dic1.Add(rd.GetString(0).Trim(), rd.GetString(1) + ";" + rd.GetByte(2).ToString());

                if (!dic2.ContainsKey(rd.GetString(0)))
                    dic2.Add(rd.GetString(0), rd.GetString(0) + ";" + rd.GetByte(1).ToString());
            }
            rd.Close();
            cn.Close();


            string barkodsql = "SELECT     product_code,barcode  FROM ORDER_DETAILS where product_code is not null GROUP BY barcode,product_code";
            DataTable dt = Factory.GetFactory.ExecuteDataTable(barkodsql);
            string stokkod = "";
            string barkod = "";
            if (dt.Rows.Count > 0)
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    stokkod = dt.Rows[i][0].ToString().Trim();
                    barkod = dt.Rows[i][1].ToString().Trim();

                    //if (dic1.ContainsKey(barkod))
                    //{
                    //    if (!stokkodlari.ContainsKey(barkod))
                    //        stokkodlari.Add(barkod, dic1[barkod].Split(';'));
                    //}

                    if (dic2.ContainsKey(stokkod))
                    {
                        if (!stokkodlari.ContainsKey(stokkod))
                            stokkodlari.Add(stokkod, dic2[stokkod].Split(';'));
                    }
                }
        }

        SqlConnection tempCn;
        SqlCommand tempCmd;

        int mikroEvrakSira(string seri)
        {
            if (tempCn == null)
            {
                tempCn = new SqlConnection(string.Format("Data Source={0};Database={1};user id=sa;password={2}", this.MikroServer, this.MikroDb, this.Sifre));
                tempCn.Open();
                tempCmd = new SqlCommand();
                tempCmd.Connection = tempCn;
            }

            tempCmd.CommandText = string.Format("exec sp_executesql N'SELECT  max(sip_evrakno_sira) FROM  SIPARISLER  WHERE     (sip_evrakno_seri =''{0}'')'", seri);
            object deger = tempCmd.ExecuteScalar();

            if (deger == null || deger == DBNull.Value)
                return 1;
            else
                return Convert.ToInt32(deger) + 1;
        }


        string VerilenSiparisEkle(DateTime evrak_tarih, string seri, int sira, int satir, string stok_kod, string cari_kod, decimal miktar, string giris_depo, string cikis_depo, string plasiyer, string srmerkez, string kullanici, decimal yekun, decimal isk1tutar, decimal isk2tutar, decimal isk3tutar, decimal isk4tutar, decimal isk5tutar, int kdv, decimal fiyat, string belge_no, string firma_no, string sube_no, int doviz)
        {
            string sonuc = "";
            try
            {
                SqlCommand cmd = new SqlCommand("SIPARIS_EKLE", MikroCnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = int.MaxValue;
                cmd.Parameters.Add("@Evrak_Tarih", SqlDbType.DateTime).Value = evrak_tarih;
                cmd.Parameters.Add("@Seri", SqlDbType.VarChar).Value = seri;
                cmd.Parameters.Add("@Sira", SqlDbType.Int).Value = sira;
                cmd.Parameters.Add("@Satir", SqlDbType.Int).Value = satir;
                cmd.Parameters.Add("@Stok_kod", SqlDbType.VarChar).Value = stok_kod;

                string cari = cari_kod;
                if (cari.Length > 3)
                {
                    cari = cari.Insert(3, ".");
                }
                if (cari.Length > 5)
                {
                    cari = cari.Insert(6, ".");
                }
                cmd.Parameters.Add("@Cari_kod", SqlDbType.VarChar).Value = cari;
                cmd.Parameters.Add("@Miktar", SqlDbType.Decimal).Value = miktar;
                cmd.Parameters.Add("@GirisDepo", SqlDbType.Int).Value = giris_depo;
                cmd.Parameters.Add("@CikisDepo", SqlDbType.Int).Value = cikis_depo;
                cmd.Parameters.Add("@plasiyer", SqlDbType.VarChar).Value = plasiyer;
                cmd.Parameters.Add("@srmerkez", SqlDbType.VarChar).Value = srmerkez;
                cmd.Parameters.Add("@kullanici", SqlDbType.VarChar).Value = kullanici;
                cmd.Parameters.Add("@yekun", SqlDbType.Decimal).Value = yekun;
                cmd.Parameters.Add("@isk1_tutar", SqlDbType.Decimal).Value = isk1tutar;
                cmd.Parameters.Add("@isk2_tutar", SqlDbType.Decimal).Value = isk2tutar;
                cmd.Parameters.Add("@isk3_tutar", SqlDbType.Decimal).Value = isk3tutar;
                cmd.Parameters.Add("@isk4_tutar", SqlDbType.Decimal).Value = isk4tutar;
                cmd.Parameters.Add("@isk5_tutar", SqlDbType.Decimal).Value = isk5tutar;
                cmd.Parameters.Add("@Kdv", SqlDbType.Int).Value = kdv;
                cmd.Parameters.Add("@Fiyat", SqlDbType.Decimal).Value = fiyat;
                cmd.Parameters.Add("@belge_no", SqlDbType.VarChar, 25).Value = belge_no;
                cmd.Parameters.Add("@firma_no", SqlDbType.Int).Value = int.Parse(firma_no);
                cmd.Parameters.Add("@sube_no", SqlDbType.Int).Value = int.Parse(sube_no);
                cmd.Parameters.Add("@doviz", SqlDbType.Int).Value = doviz;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                sonuc = ex.Message;
            }
            return sonuc;
        }
    }
}
